SQL Injection Defense for Shiny Applications

Injection Attacks = Child’s Play

The Open Web Application Security Project publishes a Top 10 Web Application Security Risks. The list guides developers seeking to improve web-app security. Injection based attacks have held the number one spot since 2010.

Why is injection at the top? For one, Hackers discover vulnerable sites with little effort. Tools like Havij and Shodan make injection attacks [child’s play] (https://techcrunch.com/2018/08/12/hacking-the-websites-responsible-for-election-information-is-so-easy-an-11-year-old-did-it/). The potential impact from injection based attacks, especially SQL injection, is severe. SQL injection vulnerabilities allow hackers to circumvent security controls. and run arbitrary scripts against the database. These scripts might steal data, destroy data, create a backdoor or all the above. Despite the ease and severity organizations still have trouble eliminating the threat. The news article featured in the GIF below were all written in the last two years. They are a few examples of SQL injection that I found interesting.

Large organizations like Cisco, Instagram and Texas.Gov still find injection vulnerabilities. MySQL and PostgreSQL write source code for database systems but even they are not immune. If these companies write vulnerable code then I’m capable writing vulnerable code. Baring a major a coding paradigm shift, the risk for injection will not disappear.

Breaking Down a Shiny App

The goal of this post is to show how to defend a Shiny app from SQL injection. I built a simple shiny app to illustrate. The app takes an email address and job title as inputs and saves them to a SQL Server database. Users can also update and delete existing records. The app persists user input in one table named dbo.Persons. The following SQL creates dbo.Persons and then inserts one row. The last statement selects all the rows.

SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Persons;
CREATE TABLE dbo.Persons (
  email Varchar(100) PRIMARY KEY, 
  jobtitle Varchar(100)
);

INSERT INTO dbo.Persons VALUES ('q1724449@nwytg.net','Analyst');

SELECT email, jobtitle FROM dbo.Persons;

The shiny UI controls provide input to the following queries. R combines the user input and query code.

-- Select all rows from the dbo.Persons table
SELECT * FROM dbo.Persons

--Get the jobtitle where the email address matches the user provided email
SELECT jobtitle FROM dbo.Persons WHERE email = 'User Input'

--Update the Email and job title where the email address matches the user provided email
UPDATE dbo.Persons SET Email = 'User Input',jobtitle=  'User Input' WHERE email ='User Input')

--Delete a row where the email matches the user provided email
DELETE FROM dbo.Persons WHERE Email = 'User Input'

--add a new row to the dbo.Persons table based on user provided email and jobtitle
INSERT INTO dbo.Persons(email,jobtitle) VALUES ('User Input','User Input')

SELECT, UPDATE, DELETE, and INSERT are the fundamental operations of data modification language (DML). My sample app only executes DML code. The other types of query code are data control language (DCL) and data definition language (DDL). I don’t see a lot of apps using DCL and DDL but there’re times when it’s appropriate. The CREATE TABLE query above is an example of DDL. The code below concatenates the user input with query code and the execute the statement.

    jt <- dbGetQuery(myPool,
                     paste0("Select jobtitle From dbo.Persons Where email   ='",input$titled,"'"))

          dbGetQuery(myPool,
                     paste0("UPDATE dbo.Persons SET Email = '",input$emailupdate,"',jobtitle=  '",input$titleupdate,"'
                             Where email ='",input$titled,"'"))
          
          dbGetQuery(myPool,
                     paste0("DELETE From dbo.Persons WHERE Email = '",input$titled,"'"))
          
          dbGetQuery(myPool
                     ,paste0("Insert into dbo.Persons values ('",input$email,"','",input$title,"')"))

The code is vulnerable to SQL injection. There is nothing stopping a user from passing a SQL script to either one of the inputs. The GIF below first shows how a user a suppose to interact with the app. Then it shows how a hacker can pass a malicious script through the email input field.

The string executed against the database is two separate SQL commands. The second command truncates the Persons table. In a real world production system this might be data loss or application downtime.

email <- "'','') TRUNCATE TABLE dbo.Persons --"
title <- "Analyst"
paste0("Insert into dbo.Persons values ('",email,"','",title,"')")

## [1] "Insert into dbo.Persons values (''','') TRUNCATE TABLE dbo.Persons --','Analyst')"

Defending Against Injection

Paramertization and String Escaping

The app mixes trusted data with untrusted data. The trusted data is the app and query code. The untrusted data is user input. If untrusted data mixes with trusted data then query code becomes changeable at run time. I can start to separate the trusted and untrusted data through parameterised queries. Instead of passing the user input variables to the query string, I store them in parameters. Then I reference the parameters instead of the user variables.

  query <- sqlInterpolate(con,"Insert into dbo.Persons values (?email,?title)",input$email,input$title))

  dbGetQuery(myPool, query)

The sqlInterpolate function helps isolate the user input from the query string. Also, sqlinterpolate escapes single tick marks. This makes it more difficult to execute an ad-hoc scripts. The injection attack fails because the code handles input as one continuous string.

emailwhitelist <- "^[[:alnum:].-_]+@[[:alnum:].-]+$"

   if(!is.na(str_match(input$email, emailwhitelist))){
    query <- sqlInterpolate(con,
                            "INSERT INTO dbo.Persons 
                            VALUES(?email,?title)",input$email,input$title))
  dbGetQuery(myPool, query)
     } else {stop("Not a valid email.")}

Use Cab_Demo
GO

DROP PROCEDURE IF EXISTS dbo.uspInsertEmail;
GO
CREATE PROCEDURE dbo.uspInsertEmail
@email Varchar(100),
@title Varchar(100)
AS
INSERT INTO dbo.Persons
VALUES (@email,@title)

GO

GRANT EXECUTE ON dbo.uspInsertEmail TO shinybuilder_app 

  query <- sqlInterpolate(con
                          ,"EXECUTE dbo.uspInsertEmail ?email,?title",input$email,input$title))

  dbGetQuery(myPool, query)

Defense in Depth

The more complicated the app the harder to manage injection risks. Even the big firms struggle. No code can guarantee 100% security. But parametrization and whitelists are good first lines of defense.